CREATE OR REPLACE EDITIONABLE PACKAGE BODY "SCOTT"."PKG_INDEX_WEEK" is
  /*--------------------------------------- 计算表STOCK_INDEX_WEEK的所有记录 ---------------------------------------*/
  procedure CALCULATE_STOCK_INDEX_WEEK is
    -- 表示code_字段
    v_index_code varchar2(10);
    -- 表示下一个星期一
    v_first_next_monday date;
    -- 定义一个含有5个stock_index记录的数组
    type stock_index_week_type_array is varray(10000) of stock_index%rowtype;
    t_stock_index_week_record stock_index_week_type_array := stock_index_week_type_array();
    -- 表示一个星期中的开盘价，收盘价，最高价和最低价
    v_stockindexweek_open_price    number;
    v_stockindexweek_close_price   number;
    v_stockindexweek_highest_price number;
    v_stockindexweek_lowest_price  number;
    -- 表示这个星期的交易日数
    v_day_num_in_week number;
    -- 表示这个星期这只指数的总成交量
    v_week_volume number;
    -- 表示这个星期这只指数的总成交额
    v_week_turnover number;
    -- 表示这个星期这只指数的涨跌额
    v_week_change_amount number;
    -- 表示这个星期这只指数的涨跌幅
    v_week_change_range number;
    -- 上一周的收盘价
    v_last_index_week_close_price number;
    -- 查询所有的指数的code_，去除重复。
    cursor cur_all_index_code is
      select distinct t.code_ from stock_index t;
    -- 根据变量v_index_code查找相应的指数的所有记录，并按升序排列
    cursor cur_single_stock_index is
      select *
        from stock_index t
       where t.code_ = v_index_code
       order by t.date_ asc;
    -- 表示STOCK_INDEX类型的记录
    rt_single_stock_index stock_index%rowtype;
  begin
    -- 需要先给v_week_volume和v_week_turnover赋初值，否则第一个指数的第一个交易周将没有成交量、成交额
    v_week_volume   := 0;
    v_week_turnover := 0;
  
    -- 计算普通K线的开盘价，收盘价，最高价和最低价
    open cur_all_index_code;
    loop
      fetch cur_all_index_code
        into v_index_code;
      exit when cur_all_index_code%notfound;
    
      -- v_first_next_monday。next_day函数的第二个参数为1时表示星期日，为2时才表示星期一。
      select *
        into v_first_next_monday
        from (select next_day(t.date_, 2)
                from stock_index t
               where t.code_ = v_index_code
               order by t.date_ asc)
       where rownum <= 1;
    
      -- 注意：如果不添加这一行，每个指数都会出现开始日期大于结束日期的记录（第一条记录除外）
      t_stock_index_week_record := stock_index_week_type_array();
    
      open cur_single_stock_index;
      loop
        fetch cur_single_stock_index
          into rt_single_stock_index;
        exit when cur_single_stock_index%notfound;
      
        if rt_single_stock_index.date_ < v_first_next_monday then
          -- 如果是属于同一个星期的记录就保存到数组中
          t_stock_index_week_record.extend;
          t_stock_index_week_record(t_stock_index_week_record.count) := rt_single_stock_index;
        else
          -- 计算一个星期中的开盘价，收盘价，最高价和最低价
          v_stockindexweek_open_price  := t_stock_index_week_record(1)
                                          .open_price;
          v_stockindexweek_close_price := t_stock_index_week_record(t_stock_index_week_record.count)
                                          .close_price;
          if v_stockindexweek_open_price > v_stockindexweek_close_price then
            v_stockindexweek_highest_price := v_stockindexweek_open_price;
            v_stockindexweek_lowest_price  := v_stockindexweek_close_price;
          end if;
          if v_stockindexweek_open_price < v_stockindexweek_close_price then
            v_stockindexweek_highest_price := v_stockindexweek_close_price;
            v_stockindexweek_lowest_price  := v_stockindexweek_open_price;
          end if;
          for x in 1 .. t_stock_index_week_record.count loop
            if t_stock_index_week_record(x)
             .highest_price > v_stockindexweek_highest_price then
              v_stockindexweek_highest_price := t_stock_index_week_record(x)
                                                .highest_price;
            end if;
          end loop;
          for x in 1 .. t_stock_index_week_record.count loop
            if t_stock_index_week_record(x)
             .lowest_price < v_stockindexweek_lowest_price then
              v_stockindexweek_lowest_price := t_stock_index_week_record(x)
                                               .lowest_price;
            end if;
          end loop;
          -- 这个星期这只指数的总成交量、成交额
          for x in 1 .. t_stock_index_week_record.count loop
            v_week_volume   := v_week_volume + t_stock_index_week_record(x)
                              .volume;
            v_week_turnover := v_week_turnover + t_stock_index_week_record(x)
                              .turnover;
          end loop;
          -- 这个星期这只指数的涨跌额、涨跌幅
          if v_last_index_week_close_price is null then
            v_week_change_amount := v_stockindexweek_close_price -
                                    v_stockindexweek_open_price;
            v_week_change_range  := v_week_change_amount /
                                    v_stockindexweek_open_price * 100;
          else
            v_week_change_amount := v_stockindexweek_close_price -
                                    v_last_index_week_close_price;
            v_week_change_range  := v_week_change_amount /
                                    v_last_index_week_close_price * 100;
          end if;
        
          -- 插入这个星期的数据
          v_day_num_in_week := t_stock_index_week_record.count;
          insert into stock_index_week
            (CODE_,
             BEGIN_DATE,
             END_DATE,
             OPEN_PRICE,
             CLOSE_PRICE,
             HIGHEST_PRICE,
             LOWEST_PRICE,
             CHANGE_AMOUNT,
             CHANGE_RANGE,
             VOLUME,
             TURNOVER,
             HA_INDEX_WEEK_OPEN_PRICE,
             HA_INDEX_WEEK_CLOSE_PRICE,
             HA_INDEX_WEEK_HIGHEST_PRICE,
             HA_INDEX_WEEK_LOWEST_PRICE)
          values
            (v_index_code,
             t_stock_index_week_record     (1).date_,
             t_stock_index_week_record     (v_day_num_in_week).date_,
             v_stockindexweek_open_price,
             v_stockindexweek_close_price,
             v_stockindexweek_highest_price,
             v_stockindexweek_lowest_price,
             v_week_change_amount,
             v_week_change_range,
             v_week_volume,
             v_week_turnover,
             null,
             null,
             null,
             null);
          commit;
          -- 为下一轮迭代做准备
          select next_day(rt_single_stock_index.date_, 2)
            into v_first_next_monday
            from dual;
          t_stock_index_week_record := stock_index_week_type_array();
          t_stock_index_week_record.extend;
          t_stock_index_week_record(t_stock_index_week_record.count) := rt_single_stock_index;
          v_week_volume := 0;
          v_week_turnover := 0;
          v_last_index_week_close_price := v_stockindexweek_close_price;
        end if;
      end loop;
      close cur_single_stock_index;
    
    end loop;
    close cur_all_index_code;
  end CALCULATE_STOCK_INDEX_WEEK;

  ---------------------------------- 计算表STOCK_INDEX_WEEK的所有Hei Kin Ashi字段 -----------------------------
  procedure CALCULATE_STOCK_INDEX_WEEK_HA is
    -- 指数代码
    v_index_code varchar2(10);
    -- 结束日期
    v_end_date date;
    -- 获取所有的INDEX_CODE
    cursor cur_all_index_code is
      select distinct t.code_ index_code from stock_index t;
    -- 查询某个具体的指数的第一条交易记录
    cursor cur_fist_stock_index_week is
      select *
        from (select *
                from stock_index_week t
               where t.code_ = v_index_code
               order by t.end_date asc)
       where rownum <= 1;
    -- 查询某个指数（周线级别）除了最早的一条记录外的其他记录，并按升序排列
    cursor cur_later_stock_index_week is
      select *
        from stock_index_week t
       where t.code_ = v_index_code
         and t.end_date > v_end_date
       order by t.end_date asc;
    -- 定义表stock_index_week结构的游标变量
    first_stock_index_week stock_index_week%rowtype;
    later_stock_index_week stock_index_week%rowtype;
    pre_stock_index_week   stock_index_week%rowtype;
    -- 用于计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价的变量
    v_ha_index_open_price    number;
    v_ha_index_close_price   number;
    v_ha_index_highest_price number;
    v_ha_index_lowest_price  number;
  begin
    open cur_all_index_code;
    loop
      -- 获取每个指数的index_code字段
      fetch cur_all_index_code
        into v_index_code;
      exit when cur_all_index_code%notfound;
    
      -- 先计算每个指数的第一条记录
      open cur_fist_stock_index_week;
      fetch cur_fist_stock_index_week
        into first_stock_index_week;
      exit when cur_fist_stock_index_week%notfound;
    
      -- 计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价
      v_ha_index_open_price  := (first_stock_index_week.open_price +
                                first_stock_index_week.close_price) / 2;
      v_ha_index_close_price := (first_stock_index_week.open_price +
                                first_stock_index_week.close_price +
                                first_stock_index_week.highest_price +
                                first_stock_index_week.lowest_price) / 4;
      if first_stock_index_week.highest_price > v_ha_index_open_price then
        v_ha_index_highest_price := first_stock_index_week.highest_price;
      else
        v_ha_index_highest_price := v_ha_index_open_price;
      end if;
      if first_stock_index_week.lowest_price < v_ha_index_open_price then
        v_ha_index_lowest_price := first_stock_index_week.lowest_price;
      else
        v_ha_index_lowest_price := v_ha_index_open_price;
      end if;
      -- 保存hei kin ashi平均K线开盘价，收盘价，最高价和最低价
      update stock_index_week t
         set t.ha_index_week_open_price    = v_ha_index_open_price,
             t.ha_index_week_close_price   = v_ha_index_close_price,
             t.ha_index_week_highest_price = v_ha_index_highest_price,
             t.ha_index_week_lowest_price  = v_ha_index_lowest_price
       where t.code_ = first_stock_index_week.code_
         and t.end_date = first_stock_index_week.end_date;
      commit;
      v_end_date := first_stock_index_week.end_date;
      close cur_fist_stock_index_week;
    
      -- 再计算每个指数的其他记录
      open cur_later_stock_index_week;
      loop
        fetch cur_later_stock_index_week
          into later_stock_index_week;
        exit when cur_later_stock_index_week%notfound;
      
        -- 前一条记录
        select *
          into pre_stock_index_week
          from (select *
                  from stock_index_week t
                 where t.code_ = v_index_code
                   and t.end_date <= v_end_date
                 order by t.end_date desc)
         where rownum <= 1;
      
        -- 计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价
        v_ha_index_open_price  := (pre_stock_index_week.ha_index_week_open_price +
                                  pre_stock_index_week.ha_index_week_close_price) / 2;
        v_ha_index_close_price := (later_stock_index_week.open_price +
                                  later_stock_index_week.close_price +
                                  later_stock_index_week.highest_price +
                                  later_stock_index_week.lowest_price) / 4;
        if later_stock_index_week.highest_price > v_ha_index_open_price then
          v_ha_index_highest_price := later_stock_index_week.highest_price;
        else
          v_ha_index_highest_price := v_ha_index_open_price;
        end if;
        if later_stock_index_week.lowest_price < v_ha_index_open_price then
          v_ha_index_lowest_price := later_stock_index_week.lowest_price;
        else
          v_ha_index_lowest_price := v_ha_index_open_price;
        end if;
        -- 保存hei kin ashi平均K线开盘价，收盘价，最高价和最低价
        update stock_index_week t
           set t.ha_index_week_open_price    = v_ha_index_open_price,
               t.ha_index_week_close_price   = v_ha_index_close_price,
               t.ha_index_week_highest_price = v_ha_index_highest_price,
               t.ha_index_week_lowest_price  = v_ha_index_lowest_price
         where t.code_ = later_stock_index_week.code_
           and t.end_date = later_stock_index_week.end_date;
        commit;
      
        -- 把这次的数据留给下一次迭代使用
        v_end_date := later_stock_index_week.end_date;
      end loop;
      close cur_later_stock_index_week;
    
    end loop;
    close cur_all_index_code;
  end CALCULATE_STOCK_INDEX_WEEK_HA;

  /*-------------------------------- 按照日期，计算表STOCK_INDEX_WEEK的所有记录 ---------------------------------*/
  procedure CAL_STOCK_INDEX_WEEK_BY_DATE(p_begin_date in varchar2,
                                         p_end_date   in varchar2) is
    -- 表示某个指数的INDEX_CODE字段
    v_index_code varchar2(10);
    -- 分别代表某只股票在某个星期的最高价，最低价，开盘价，收盘价，上一个交易日的收盘价，
    -- 涨跌额，涨跌幅，成交量，成交额和序列号
    v_highest_price    number;
    v_lowest_price     number;
    v_open_price       number;
    v_close_price      number;
    v_last_close_price number;
    v_change_amount    number;
    v_change_range     number;
    v_sum_volume       number;
    v_sum_turnover     number;
    -- 查询从开始时间p_begin_date到p_end_date，表STOCK_INDEX中的记录数
    v_num number;
    -- 获取表STOCK_INDEX中的列INDEX_CODE，去除重复
    cursor cur_all_index_code is
      select distinct t.code_ from stock_index t order by t.code_ asc;
  begin
    open cur_all_index_code;
    loop
      fetch cur_all_index_code
        into v_index_code;
      exit when cur_all_index_code%notfound;
    
      -- 查询从开始时间p_begin_date到p_end_date，表STOCK_INDEX中的记录数
      select count(*)
        into v_num
        from stock_index t
       where t.code_ = v_index_code
         and t.date_ between to_date(p_begin_date, 'yyyy-mm-dd') and
             to_date(p_end_date, 'yyyy-mm-dd');
    
      if v_num != 0 then
        -- 计算某只股票在某个星期的最高价，最低价，开盘价，收盘价，上一个交易日的收盘价，
        -- 涨跌额，涨跌幅，成交量和成交额
        select max(t.highest_price), min(t.lowest_price)
          into v_highest_price, v_lowest_price
          from stock_index t
         where t.code_ = v_index_code
           and t.date_ between to_date(p_begin_date, 'yyyy-mm-dd') and
               to_date(p_end_date, 'yyyy-mm-dd')
         order by t.date_ asc;
      
        select t.open_price
          into v_open_price
          from stock_index t
         where t.code_ = v_index_code
           and t.date_ between to_date(p_begin_date, 'yyyy-mm-dd') and
               to_date(p_end_date, 'yyyy-mm-dd')
           and rownum <= 1
         order by t.date_ asc;
      
        select t.close_price
          into v_close_price
          from stock_index t
         where t.code_ = v_index_code
           and t.date_ between to_date(p_begin_date, 'yyyy-mm-dd') and
               to_date(p_end_date, 'yyyy-mm-dd')
           and rownum <= 1
         order by t.date_ desc;
      
        select *
          into v_last_close_price
          from (select t.close_price
                  from stock_index t
                 where t.code_ = v_index_code
                   and t.date_ < to_date(p_begin_date, 'yyyy-mm-dd')
                 order by t.date_ desc) t1
         where rownum <= 1;
      
        if v_last_close_price is null then
          v_change_amount := v_close_price - v_open_price;
          v_change_range  := (v_close_price - v_open_price) / v_open_price * 100;
        else
          v_change_amount := v_close_price - v_last_close_price;
          v_change_range  := (v_close_price - v_last_close_price) /
                             v_last_close_price * 100;
        end if;
      
        select sum(t.volume)
          into v_sum_volume
          from stock_index t
         where t.code_ = v_index_code
           and t.date_ between to_date(p_begin_date, 'yyyy-mm-dd') and
               to_date(p_end_date, 'yyyy-mm-dd');
      
        select sum(t.turnover)
          into v_sum_turnover
          from stock_index t
         where t.code_ = v_index_code
           and t.date_ between to_date(p_begin_date, 'yyyy-mm-dd') and
               to_date(p_end_date, 'yyyy-mm-dd');
      
        insert into stock_index_week
          (CODE_,
           BEGIN_DATE,
           END_DATE,
           OPEN_PRICE,
           CLOSE_PRICE,
           HIGHEST_PRICE,
           LOWEST_PRICE,
           CHANGE_AMOUNT,
           CHANGE_RANGE,
           VOLUME,
           TURNOVER)
        values
          (v_index_code,
           to_date(p_begin_date, 'yyyy-mm-dd'),
           to_date(p_end_date, 'yyyy-mm-dd'),
           v_open_price,
           v_close_price,
           v_highest_price,
           v_lowest_price,
           v_change_amount,
           v_change_range,
           v_sum_volume,
           v_sum_turnover);
        commit;
      end if;
    end loop;
    close cur_all_index_code;
  end CAL_STOCK_INDEX_WEEK_BY_DATE;

  /*--------------------------- 按照日期，计算表STOCK_INDEX_WEEKEND的Hei Kin Ashi记录 --------------------------------*/
  procedure CAL_STOCKINDEXWEEK_HA_BY_DATE(p_begin_date in varchar2,
                                          p_end_date   in varchar2) is
    -- 获取所有的INDEX_CODE
    cursor cur_all_index_code is
      select distinct t.code_ index_code from stock_index_week t;
    -- 表示INDEX_CODE类型的变量
    v_index_code varchar2(10);
  
    -- 查询某个指数在日期p_index_date之前的那一条记录
    cursor cur_all_stock_index_week is
      select *
        from (select *
                from (select *
                        from stock_index_week t
                       where t.code_ = v_index_code
                         and t.end_date <= to_date(p_end_date, 'yyyy-mm-dd')
                         and t.begin_date <=
                             to_date(p_begin_date, 'yyyy-mm-dd')
                       order by t.end_date desc)
               where rownum <= 2) t2
       where t2.end_date <>
             (select t1.end_date
                from stock_index_week t1
               where t1.code_ = v_index_code
                 and t1.end_date = to_date(p_end_date, 'yyyy-mm-dd'))
         and t2.begin_date <>
             (select t1.begin_date
                from stock_index_week t1
               where t1.code_ = v_index_code
                 and t1.begin_date = to_date(p_begin_date, 'yyyy-mm-dd'));
    -- 定义表stock_index_week结构的游标变量，用于接收游标cur_later_stock_index_week的记录
    all_stock_index_week stock_index_week%rowtype;
  
    -- 查询某个具体的指数的某一日交易记录
    cursor cur_later_stock_index_week is
      select *
        from stock_index_week t
       where t.code_ = v_index_code
         and t.begin_date = to_date(p_begin_date, 'yyyy-mm-dd')
         and t.end_date = to_date(p_end_date, 'yyyy-mm-dd');
    -- 定义表stock_index_week结构的游标变量，用于接收游标cur_all_stock_index_week的记录
    later_stock_index_week stock_index_week%rowtype;
  
    -- 用于计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价的变量
    v_ha_index_week_open_price    number;
    v_ha_index_week_close_price   number;
    v_ha_index_week_highest_price number;
    v_ha_index_week_lowest_price  number;
  begin
    open cur_all_index_code;
    loop
      -- 获取每个指数的index_code字段
      fetch cur_all_index_code
        into v_index_code;
      exit when cur_all_index_code%notfound;
    
      open cur_all_stock_index_week;
      open cur_later_stock_index_week;
      loop
        fetch cur_all_stock_index_week
          into all_stock_index_week;
        exit when cur_all_stock_index_week%notfound;
      
        fetch cur_later_stock_index_week
          into later_stock_index_week;
        exit when cur_later_stock_index_week%notfound;
      
        -- 计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价
        v_ha_index_week_open_price  := (all_stock_index_week.ha_index_week_open_price +
                                       all_stock_index_week.ha_index_week_close_price) / 2;
        v_ha_index_week_close_price := (later_stock_index_week.open_price +
                                       later_stock_index_week.close_price +
                                       later_stock_index_week.highest_price +
                                       later_stock_index_week.lowest_price) / 4;
        if later_stock_index_week.highest_price >
           v_ha_index_week_open_price then
          v_ha_index_week_highest_price := later_stock_index_week.highest_price;
        else
          v_ha_index_week_highest_price := v_ha_index_week_open_price;
        end if;
        if later_stock_index_week.lowest_price < v_ha_index_week_open_price then
          v_ha_index_week_lowest_price := later_stock_index_week.lowest_price;
        else
          v_ha_index_week_lowest_price := v_ha_index_week_open_price;
        end if;
        -- 保存hei kin ashi平均K线开盘价，收盘价，最高价和最低价
        update stock_index_week t
           set t.ha_index_week_open_price    = v_ha_index_week_open_price,
               t.ha_index_week_close_price   = v_ha_index_week_close_price,
               t.ha_index_week_highest_price = v_ha_index_week_highest_price,
               t.ha_index_week_lowest_price  = v_ha_index_week_lowest_price
         where t.code_ = later_stock_index_week.code_
           and t.begin_date = later_stock_index_week.begin_date
           and t.end_date = later_stock_index_week.end_date;
        commit;
      end loop;
      close cur_all_stock_index_week;
      close cur_later_stock_index_week;
    
    end loop;
    close cur_all_index_code;
  end CAL_STOCKINDEXWEEK_HA_BY_DATE;

end PKG_INDEX_WEEK;